using System;
using System.Data;
using System.Data.Common;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Data;

using ctsu.Data;

namespace ctsu.Admin.BusLogic
{

	public class ProtocolUtils
	{
		public ProtocolUtils()
		{

		}

		public static int GetPatientActualProjectedPercent(int id, out string message)
		{
			message = "";
			int prj = GetProjectedPatientNumber(id, out message);

			if (message.Length > 0)
				return 0;

			if (prj < 1)
				return 0;

			int act = GetActualPatientNumber(id, out message);

			if (message.Length > 0)
				return 0;

			int percent = (act * 100) / prj;

			if (percent > 95)
			{
				if (act > prj)
				{
					int excd = act - prj;
					message = "Warning: Actual number of patients exceed projected by " + excd + ".";
				}
				else
					message = "Warning: Actual number of patients is " + percent + " percent of projected.";
			}
			return percent;
		}

		public static int GetProjectedPatientNumber(int protoID, out string message)
		{
			message = "";
			StringBuilder sb = new StringBuilder();
			int num = 0;

			string sql = "SELECT ProjectedPatients FROM Protocol " +
				" WHERE ID=" + protoID;

			//SqlConnection conn = LoginForm.GetSqlConnection();
			IDataReader reader = null;
			try
			{
				reader = DataAccess.GetDataReader(sql);
				if (reader.Read())
				{
					if (!reader.IsDBNull(0))
						num = reader.GetInt32(0);
				}

				return num;
			}
			catch (Exception ex)
			{
				sb.Append("Error in retrieving actual out-patient visit count!");
				sb.Append(Environment.NewLine);
				sb.Append("Error message: " + ex.Message);
				//bool b = ////ExceptionPolicy.HandleException(ex, "Exception Policy");
				message = sb.ToString();
				return 0;
			}
			finally
			{
				if (reader != null)
					reader.Close();
				//if(conn != null)
				//    conn.Dispose();				
			}
		}

		public static int GetActualPatientNumber(int protoID, out string message)
		{
			StringBuilder sb = new StringBuilder();
			message = "";
			int numPatients = 0;
			string sql = "SELECT Patient INTO #TPat FROM ProtocolVisitAdmission " +
				" WHERE Protocol=" + protoID;

			Database db = DatabaseFactory.CreateDatabase();
			DbConnection conn = db.CreateConnection();
			DbCommand cmd = db.GetSqlStringCommand(sql);
			cmd.Connection = conn;
			try
			{

				//the connection must be open in order to work with the same temp table				
				cmd.Connection.Open();
				cmd.CommandType = CommandType.Text;
				cmd.ExecuteNonQuery();

				sql = "INSERT INTO #TPat " +
					"SELECT Patient FROM ProtocolVisitOutPatient " +
					" WHERE Protocol=" + protoID;
				cmd.CommandText = sql;
				cmd.ExecuteNonQuery();

				sql = "INSERT INTO #TPat " +
					"SELECT Patient FROM ScatterNurseHours " +
					" WHERE Protocol=" + protoID;
				cmd.CommandText = sql;
				cmd.ExecuteNonQuery();

				sql = "SELECT COUNT(DISTINCT Patient) FROM #TPat";
				cmd.CommandText = sql;
				numPatients = (int)cmd.ExecuteScalar();

				return numPatients;
			}
			catch (Exception ex)
			{
				sb.Append("Error in retrieving actual patient count!");
				sb.Append(Environment.NewLine);
				sb.Append("Error message: " + ex.Message);
				//bool b = ////ExceptionPolicy.HandleException(ex, "Exception Policy");
				message = sb.ToString();
				return 0;
			}
			finally
			{
				if (cmd.Connection.State == ConnectionState.Open)
					cmd.Connection.Close();
			}
		}

		public static int GetActualOuPatientVisits(int protoID, out string message)
		{
			message = "";
			StringBuilder sb = new StringBuilder();
			int num = 0;

			string sql = "SELECT COUNT(Patient) FROM ProtocolVisitOutPatient " +
				" WHERE Protocol=" + protoID;

			//SqlConnection conn = LoginForm.GetSqlConnection();
			try
			{
				num = (int)DataAccess.GetScalarObject(CommandType.Text, sql);
				return num;
			}
			catch (Exception ex)
			{
				sb.Append("Error in retrieving actual out-patient visit count!");
				sb.Append(Environment.NewLine);
				sb.Append("Error message: " + ex.Message);
				//bool b = ////ExceptionPolicy.HandleException(ex, "Exception Policy");
				message = sb.ToString();
				return 0;
			}
			finally
			{
				//if(conn != null)
				//    conn.Dispose();
			}
		}

		public static int GetActualAdmissionVisitCount(int protoID, out string message)
		{
			message = "";
			StringBuilder sb = new StringBuilder();
			int num = 0;

			string sql = "SELECT COUNT(Patient) FROM ProtocolVisitAdmission " +
				" WHERE Protocol=" + protoID;

			//SqlConnection conn = LoginForm.GetSqlConnection();
			try
			{
				num = (int)DataAccess.GetScalarObject(CommandType.Text, sql);
				return num;
			}
			catch (Exception ex)
			{
				sb.Append("Error in retrieving actual admission visit count!");
				sb.Append(Environment.NewLine);
				sb.Append("Error message: " + ex.Message);
				//bool b = ////ExceptionPolicy.HandleException(ex, "Exception Policy");
				message = sb.ToString();
				return 0;
			}
			finally
			{
				//if(conn != null)
				//    conn.Dispose();
			}
		}
	}
}
